﻿using NewsReader.Helpers.Orm;
using NewsReader.Log;
using NewsReader.ViewModels;
using SQLite;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Windows.ApplicationModel;
using Windows.Foundation;
using Windows.Storage;

namespace NewsReader.Helpers
{
    public sealed class PersistenceHelper
    {
        private const int USER_DB_VERSION = 6;
        private const string CATEGORY_SELECT_SQL = "select id, name from categories";
        private const string CANNED_FEED_SELECT_SQL = "select f.id, f.uri, f.title, f.category_id, c.name from user_feeds f inner join categories c on f.category_id = c.id";
        private const string USER_FEED_SELECT_SQL = "select f.uri, f.title, f.category from user_feeds f";

        private const string NEWS_DB_SETTINGS = "Settings\\feed_data.sqlite";
        private const string NEWS_DB_USER = "feed_data.sqlite";

        private static object s_dbSync = new object();
        private static SQLiteConnection s_userDb;
        private static SQLiteConnection s_systemDb;

        public static SQLiteConnection GetUserDB()
        {
            lock (s_dbSync)
            {
                if (s_userDb == null)
                {
                    CreateUserDBConnection();

                    try
                    {
                        var dbInfos = s_userDb.Table<DbInfo>().ToList();

                        int dbVersion = dbInfos[0].Version;
                        if (dbVersion != USER_DB_VERSION)
                        {
                            UpgradeDBFrom(s_userDb, dbVersion);
                        }
                    }
                    catch (SQLiteException)
                    {
                        CreateNewUserDB();
                    }
                }

                return s_userDb;
            }
        }

        private static void CreateUserDBConnection()
        {
            string path = Path.Combine(ApplicationData.Current.LocalFolder.Path, NEWS_DB_USER);
            s_userDb = new SQLiteConnection(path, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.FullMutex | SQLiteOpenFlags.Create, true);
        }
        private static SQLiteConnection GetSystemDb()
        {
            lock (s_dbSync)
            {
                if (s_systemDb == null)
                {
                    string path = Path.Combine(Package.Current.InstalledLocation.Path, NEWS_DB_SETTINGS);
                    s_systemDb = new SQLiteConnection(path, SQLiteOpenFlags.ReadOnly | SQLiteOpenFlags.FullMutex);
                }
                return s_systemDb;
            }
        }



        public static IList<NewsFormatter> GetNewsFormatters()
        {
            var db = PersistenceHelper.GetSystemDb();
            return db.Query<NewsFormatter>("SELECT * FROM news_formatters").ToList();
        }

        public static IList<UserSettings> GetUserSettings()
        {
            var db = PersistenceHelper.GetUserDB();
            return db.Table<UserSettings>().ToList();
        }

        public static IAsyncOperation<IEnumerable<Category>> GetCategoriesAsync()
        {
            return Task.Factory.StartNew(() =>
            {
                var db = GetSystemDb();
                return (IEnumerable<Category>)db.Table<Category>().ToList();

            }).AsAsyncOperation();
        }

        public static IAsyncOperation<IEnumerable<FeedConfig>> GetCannedFeedsAsync()
        {
            return Task.Factory.StartNew(() =>
            {
                var db = GetSettingsDB();
                var configs = db.Query<FeedConfiguration>(CANNED_FEED_SELECT_SQL);
                return (IEnumerable<FeedConfig>)configs.Select(c => new FeedConfig(c.Title, c.Uri, c.CategoryName, c.CategoryId)).ToList();

            }).AsAsyncOperation();
        }

        public static IAsyncOperation<IEnumerable<FeedConfig>> GetUserFeedsAsync()
        {
            return Task.Factory.StartNew<IEnumerable<FeedConfig>>(() =>
              {
                  var t = GetCategoriesAsync().AsTask();
                  t.Wait();
                  var v = new List<FeedConfig>();
                  var categories = t.Result.ToDictionary(c => c.Name);

                  var db = GetUserDB();
                  var userFeeds = db.Query<UserFeed>("select * from user_feeds");
                  foreach (var uf in userFeeds)
                  {
                      var catName = uf.Category;
                      Category cat;
                      categories.TryGetValue(catName, out cat);

                      var fc = new FeedConfig(uf.Title, uf.Uri, catName, cat == null ? 0 : cat.Id);
                      v.Add(fc);
                  }

                  return (IEnumerable<FeedConfig>)v;
              }).AsAsyncOperation();
        }

        public static IAsyncAction DeleteFeedsFromDBAsync()
        {
            Logger.Log.Debug("Deleting old feeds");
            return Task.Factory.StartNew(() =>
            {
                var db = GetUserDB();
                var feedsToDelete = db.Query<Feed>("select * from feeds where uri not in (select uri from user_feeds)");
                Logger.Log.InfoFormat("Found {0} feeds to delete", feedsToDelete.Count);

                if (feedsToDelete.Count == 0)
                {
                    return;
                }
                else
                {
                    string inStatement = string.Join(",", feedsToDelete.Select(f => f.Id).ToArray());

                    var count = db.Execute(string.Format("delete from feed_items where feed_id in ({0})", inStatement));
                    Logger.Log.InfoFormat("Deleted {0} feed items", count);

                    count = db.Execute(string.Format("delete from feeds where id in ({0})", inStatement));
                    Logger.Log.InfoFormat("Deleted {0} feeds", count);
                }
            }).AsAsyncAction();
        }

        public static void DeleteFeedsFromDBExcept(IEnumerable<object> existingFeeds)
        {
            var db = GetUserDB();
            db.Execute("drop table if exists existing_uri");
            db.Execute("CREATE TABLE existing_uri ('uri' text PRIMARY KEY  NOT NULL )");

            if (!IsNullOrEmpty(existingFeeds))
            {
                foreach (FeedInfo fi in existingFeeds)
                {
                    db.Execute("INSERT INTO existing_uri (uri) VALUES (?)", fi.Uri.AbsoluteUri);
                }
            }

            db.Execute("delete from feeds where uri not in (select uri from existing_uri)");
            db.Execute("delete from feed_items where feed_id not in (select id from feeds)");
            db.Execute("DROP TABLE existing_uri");
        }


        public static Task SaveFeedsToDBAsync(IEnumerable<object> feeds)
        {
            return Task.Factory.StartNew(() =>
            {
                SaveFeedsToDB(feeds);
            });
        }

        public static void SaveFeedsToDB(IEnumerable<object> feeds)
        {
            int feedCount = feeds.Count();
            Debug.WriteLine(String.Format("Saving {0} feeds.", feedCount));

            var db = GetUserDB();

            DeleteArticlesOlderThanDays(SettingsManager.Instance.GetOldestDateOfArticleSupported());

            DeleteFeedsFromDBExcept(feeds);

            foreach (var feed in feeds)
            {
                var fi = (FeedInfo)feed;
                if (fi.IsLoaded)
                {
                    SaveFeedToDB(db, fi);
                }
            }
        }

        private static void DeleteArticlesOlderThanDays(DateTimeOffset oldestDate)
        {
            var oldestArticleToKeep = oldestDate.Ticks;
            var db = GetUserDB();
            int deletedCount = db.Execute("delete from feed_items where published_date < ? ", oldestArticleToKeep);

            Logger.Log.InfoFormat("Deleted {0} feeds items because they were older than {1}", deletedCount, oldestDate);
        }

        private static void SaveFeedToDB(SQLite.SQLiteConnection db, FeedInfo feed)
        {
            Debug.WriteLine(string.Format("Saving feed {0} with URI {1}", feed.Title, feed.Uri));
            try
            {
                int feedId;
                var existingFeeds =  db.CreateCommand("select * from feeds where uri = ?", feed.Uri.AbsoluteUri).ExecuteQuery<Feed>();
                if (existingFeeds.Count == 1)
                {
                    feedId = existingFeeds[0].Id;

                    db.Execute("Update feeds set last_updated = ?, image_uri = ? where uri = ?",
                                   feed.LastUpdated.UtcDateTime,
                                    feed.ImageUri == null ? null : feed.ImageUri.AbsoluteUri,
                                    feed.Uri.AbsoluteUri);
                }
                else 
                {
                    var f = new Feed()
                    {
                        Uri = feed.Uri.AbsoluteUri,
                        Title = feed.Title,
                        ImageUri = feed.ImageUri == null ? null : feed.ImageUri.AbsoluteUri,
                        LastUpdated = feed.LastUpdated.UtcDateTime
                    };

                    db.Insert(f);
                    feedId = f.Id;
                }

                var persistedItemsForFeed = db.Table<NewsReader.Helpers.Orm.FeedItem>().Where(f => f.FeedId == feedId).ToList();
                
                foreach (var feedItem in feed.FeedItems)
                {
                    var fi = (NewsReader.ViewModels.FeedItem)feedItem;
                    if (fi.FullTextLoaded)
                    {
                        SaveFeedItemToDB(db, feedId, fi, persistedItemsForFeed);
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("Error saving feed to DB {0}", ex);
            }
        }

        private static int GetLastInsertedFeedId(SQLiteConnection db)
        {
            int feedId;
            var stmt = db.CreateCommand("SELECT last_insert_rowid()");
            feedId = stmt.ExecuteScalar<int>();
            return feedId;
        }

        private static void SaveFeedItemToDB(SQLiteConnection db, int feedId, 
            NewsReader.ViewModels.FeedItem feedItem, 
            List<NewsReader.Helpers.Orm.FeedItem> persistedItemsForFeed)
        {
            try
            {

                var persisted = persistedItemsForFeed.FirstOrDefault(pfi => pfi.SyndicationId == feedItem.Id);
                if(persisted == null)
                {
                    db.Insert(Orm.FeedItem.FromViewModel(feedId, feedItem));
                }
                else
                {
                    persisted.ImageUri = feedItem.ImageUriExists ? feedItem.ImageUri.ToString() : null;
                    persisted.Text = feedItem.FullText;
                    persisted.Summary = feedItem.Summary;

                    db.Update(persisted);
                }
            }
            catch (Exception ex)
            {
                Logger.Log.ErrorFormat("Error saving feedItem {0}", ex);
            }
        }

        public static IAsyncOperation<IEnumerable<object>> GetFeedsFromDBAsync()
        {
            return Task.Factory.StartNew(() =>
            {
                var db = GetUserDB();
                var v = new List<object>();
                foreach (var f in db.Table<Feed>())
                {
                    var fi = new FeedInfo(f.Uri, f.Title, f.ImageUri, f.LastUpdated);
                    fi.IsLoaded = true;
                    LoadFeedItemsFor(db, f.Id, fi);
                    v.Add(fi);
                }
                return (IEnumerable<object>)v;

            }).AsAsyncOperation();
        }

        private static IEnumerable<object> GetFakeNews()
        {
            List<object> news = new List<object>();
            var item = new FeedInfo("http://news.com", "Hello News 1", null, DateTimeOffset.Now);
            LoadFakeItemsFor(item);
            news.Add(item);

            item = new FeedInfo("http://news.com", "Hello News 2", null, DateTimeOffset.Now);
            LoadFakeItemsFor(item);

            news.Add(item);
            return news;
        }

        private static void LoadFakeItemsFor(FeedInfo feedInfo)
        {
            foreach (var i in Enumerable.Range(0, 20))
            {
                var fi = new NewsReader.ViewModels.FeedItem(
                        Guid.NewGuid().ToString(),
                       "Helo World " + i,
                       "Hello World Summary",
                       "http://hello.world.com",
                       null,
                       String.Concat(Enumerable.Range(1, 100).Select(line => "News " + i + " ASDja lfasdfhjklasdh fjklasdh jfhasdlfh asdjklhfljkasdh jflhasdjl fhasdjklhf jklasdhf asdjkfhasdjklh flasdjk\n").ToArray()),
                       DateTimeOffset.Now);
                feedInfo.AddFeedItem(fi);
            }
        }

        private static void LoadFeedItemsFor(SQLiteConnection db, int feedId, FeedInfo feedInfo)
        {
            var fis = db.Query<Orm.FeedItem>("SELECT id, synd_id, title, summary, uri, image_uri, text, published_date FROM feed_items where feed_id = ?", feedId);

            //String feedId, String title, String summary, String uri, String image_uri, String fullText, DateTimeOffset publishedTime)
            var vmFIs = fis.Select(fi => new NewsReader.ViewModels.FeedItem(
                fi.SyndicationId,
                fi.Title,
                fi.Summary,
                fi.Uri,
                fi.ImageUri,
                fi.Text,
                fi.PublishedDate));


            foreach (var fi in vmFIs)
            {
                feedInfo.AddFeedItem(fi);
            }
        }

        public static IAsyncAction SaveUserSelectedFeedsAsync(IEnumerable<UserFeedConfig> selectedFeeds)
        {
            return Task.Factory.StartNew(() =>
            {
                var db = GetUserDB();
                db.DeleteAll<UserFeed>();
                foreach (var uf in selectedFeeds)
                {
                    db.Insert(new UserFeed() { Uri = uf.Uri, Title = uf.Title, Category = uf.Category });
                }
            }).AsAsyncAction();
        }

        public static SQLiteConnection GetSettingsDB()
        {
            return new SQLiteConnection(NEWS_DB_SETTINGS, SQLiteOpenFlags.ReadOnly, false);
        }


        public static int GetUserDBVersion()
        {
            var db = GetUserDB();
            return db.ExecuteScalar<int>("SELECT version FROM db_info");
        }

        public static void UpgradeDBFrom(SQLiteConnection db, int dbVersion)
        {
            for (int i = dbVersion; i < USER_DB_VERSION; i++)
            {
                switch (i)
                {
                    case 2:
                        UpgradeFrom2To3(db);
                        break;
                    case 5:
                        UpgradeDBFrom5To6(db);
                        break;
                }
            }

            SetDBVersion(db, USER_DB_VERSION);
        }


        private static void UpgradeDBFrom5To6(SQLiteConnection db)
        {
            db.Execute("delete from feed_items");
            db.Execute("alter table \"feed_items\" add column created_date bigint");

            SetDBVersion(db, 6);
        }


        private static void UpgradeFrom2To3(SQLiteConnection db)
        {
            var stmt = db.CreateCommand("CREATE  TABLE feed_items (id integer PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , " +
                                           " name TEXT NOT NULL  UNIQUE , " +
                                           " value TEXT NOT NULL)");
            stmt.ExecuteNonQuery();

            SetDBVersion(db, 3);
        }

        private static void SetDBVersion(SQLiteConnection db, int version)
        {
            var stmt = db.CreateCommand(string.Format("UPDATE db_info set version = {0}, created_date = {1}", version, DateTime.UtcNow));
            stmt.ExecuteNonQuery();
        }

        public static void CreateNewUserDB()
        {
            s_userDb.Dispose();
            try
            {
                string path = Path.Combine(ApplicationData.Current.LocalFolder.Path, NEWS_DB_USER);
                StorageFile.GetFileFromPathAsync(path).AsTask().ContinueWith(fileTask =>
                {
                    return fileTask.Result.DeleteAsync().AsTask();
                }).Wait();
            }
            catch (AggregateException) { }
            CreateUserDBConnection();

            s_userDb.CreateTable<Feed>();
            s_userDb.CreateTable<NewsReader.Helpers.Orm.FeedItem>();
            s_userDb.CreateTable<UserSettings>();
            s_userDb.CreateTable<DbInfo>();
            s_userDb.CreateTable<UserFeed>();

            s_userDb.Insert(new DbInfo() { Version = USER_DB_VERSION, CreatedDate = DateTime.UtcNow });
        }

        public static bool IsNullOrEmpty(IEnumerable enumerable)
        {
            return enumerable == null ||
                    !enumerable.GetEnumerator().MoveNext();
        }
    }
}